package com.cn.sys.frame.dao.impl;

import java.lang.reflect.ParameterizedType;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import net.sf.cglib.beans.BeanMap;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.jdbc.support.incrementer.PostgreSQLSequenceMaxValueIncrementer;

import com.cn.sys.common.Constants;
import com.cn.sys.frame.dao.BaseSpringJdbcDao;
import com.cn.sys.frame.db.dialect.Dialect;
import com.cn.sys.frame.db.dialect.MysqlDialect;
import com.cn.sys.frame.db.dialect.OracleDialect;
import com.cn.sys.frame.db.dialect.PostgreSQLDialect;
import com.cn.sys.frame.db.dialect.SQLServer2005Dialect;
import com.cn.sys.frame.exception.DaoException;
import com.cn.sys.frame.util.BaseDaoUitl;
import com.cn.sys.frame.util.SpringContextUtil;
import com.cn.sys.frame.util.page.PageData;
import com.cn.sys.frame.util.query.QueryCondition;

public class BaseSpringJdbcDaoImpl<T, PK> implements BaseSpringJdbcDao<T, PK>{

	private static final Logger logger = LoggerFactory.getLogger(BaseSpringJdbcDaoImpl.class);
	
	private Class<T> entityClass;//实体类
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private NamedParameterJdbcTemplate namedJdbcTemplate;
	private String sequenceName;//序列名称
	private DataFieldMaxValueIncrementer incrementer;//序列生成器
	private Dialect dialect = null;//数据库分页类
	
	@SuppressWarnings("unchecked")
	public BaseSpringJdbcDaoImpl(){
		this.entityClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
		this.sequenceName = "SEQ_"+BaseDaoUitl.underscoreName(entityClass.getSimpleName()).toUpperCase();//默认的序列名称为SEQ_+表名称
	}
	
	@SuppressWarnings("unchecked")
	public BaseSpringJdbcDaoImpl(String sequenceName){
		this.entityClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
		this.sequenceName = sequenceName;//自定义序列名称
	}
	
	public T get(PK id) throws Exception {
		String sql = BaseDaoUitl.getSelectSql(entityClass) + " AND ID = ?";
		return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<T>(entityClass), id);
	}

	public T get(QueryCondition queryCondition) throws Exception{
		List<T> list = this.listByCondition(queryCondition);
		if(list==null||list.isEmpty()){
			return null;
		}else {
			return list.get(0);
		}
	}
	
	public T save(T entity) throws Exception {
		if(entity==null){
			logger.info("保存的实体为null!");
			return null;
		}
		if(BaseDaoUitl.checkSysDbKeySequence()){
			if(StringUtils.isBlank(sequenceName)){
				String tableName = BaseDaoUitl.underscoreName(entityClass.getSimpleName()).toUpperCase();
				throw new IllegalArgumentException("表["+tableName+"]对应的序列名称为空!");
			}
			BeanMap.create(entity).put("id", this.getIncrementer().nextLongValue());
		}
		String sql = BaseDaoUitl.getInsertSql(entityClass);
		//logger.info("插入的sql语句为:"+sql);
		SqlParameterSource param = new BeanPropertySqlParameterSource(entity);
		if(BaseDaoUitl.checkSysDbKeyIncreament()){
			 KeyHolder holder = new GeneratedKeyHolder();
			 namedJdbcTemplate.update(sql, param, holder);
			 BeanMap.create(entity).put("id", holder.getKey().longValue());
		}else if(BaseDaoUitl.checkSysDbKeySequence()){
			namedJdbcTemplate.update(sql, param);
		}else {
			entity = null;
		}
		return entity;
	}

	public T saveWithOutPK(T entity) throws Exception{
		if(entity==null){
			logger.info("保存的实体为null!");
			return null;
		}
		String sql = BaseDaoUitl.getInsertSql(entityClass);
		SqlParameterSource param = new BeanPropertySqlParameterSource(entity);
		namedJdbcTemplate.update(sql, param);
		return entity;
	}
	
	public int batchSave(Collection<T> entitys) throws Exception {
		if(entitys==null||entitys.isEmpty()){
			logger.info("批量保存的实体集合为null或者实体集合大小为0 !");
			return 0;
		}
		if(BaseDaoUitl.checkSysDbKeySequence()){
			for (T t : entitys) {
				BeanMap.create(t).put("id", this.getIncrementer().nextLongValue());
			}
		}
		String sql = BaseDaoUitl.getInsertSql(entityClass);
		SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(entitys.toArray());
		int[] count = namedJdbcTemplate.batchUpdate(sql, params);
		//logger.info("批量保存实体["+entityClass+"]"+count.length+"条数据!  ");
		return count.length;
	}

	public T update(T entity) throws Exception {
		if(entity==null){
			logger.info("更新的实体为null!");
			return null;
		}
		String sql = BaseDaoUitl.getUpdateSql(entityClass) + " WHERE ID = :id";
		SqlParameterSource param = new BeanPropertySqlParameterSource(entity);
		namedJdbcTemplate.update(sql, param);
		return entity;
	}

	public int batchUpdate(Collection<T> entitys) throws Exception {
		if(entitys==null||entitys.isEmpty()){
			logger.info("批量更新的实体集合为null或者实体集合大小为0 !");
			return 0;
		}
		String sql = BaseDaoUitl.getUpdateSql(entityClass) + " WHERE ID = :id";
		SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(entitys.toArray());
		int[] count = namedJdbcTemplate.batchUpdate(sql, params);
		//logger.info("更新实体 ["+entityClass+"]"+count.length+"条数据! ");
		return count.length;
	}

	public int delete(PK id) throws Exception {
		String sql = BaseDaoUitl.getDeleteSql(entityClass) + " WHERE ID = ?";
		return jdbcTemplate.update(sql, id);
	}

	public int delete(QueryCondition queryCondition) throws Exception{
		if(queryCondition==null||StringUtils.isBlank(queryCondition.getQueryString())){
			logger.info("queryCondition删除条件为空!");
			return 0;
		}
		String sql = BaseDaoUitl.getDeleteSql(entityClass) + queryCondition.getQueryString();
		return jdbcTemplate.update(sql, queryCondition.getQueryValues());
	}
	
	public List<T> listAll() throws Exception {
		
		return this.listByCondition(null);
	}

	public List<T> listByCondition(QueryCondition queryCondition) throws Exception{
		String sql = BaseDaoUitl.getSelectSql(entityClass);
		if(queryCondition!=null){
			if(StringUtils.isNotBlank(queryCondition.getQueryString())){
				sql += queryCondition.getQueryString();
			}
			if(StringUtils.isNotBlank(queryCondition.getOrderSql())){
				sql += queryCondition.getOrderSql();
			}
			if(queryCondition.getQueryValues()==null||queryCondition.getQueryValues().length<=0){
				return (List<T>) jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(entityClass));
			}else {
				return (List<T>) jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(entityClass), queryCondition.getQueryValues());
			}
		}else {
			return (List<T>) jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(entityClass));
		}
	}
	
	public List<T> listByCondition(String sqlCondition, Object ...values) throws Exception{
		if(StringUtils.isNotBlank(sqlCondition)){
			if(values==null||values.length<=0){
				throw new IllegalArgumentException("listByCondition() 方法参数 values为空.");
			}
			String sql = BaseDaoUitl.getSelectSql(entityClass);
			sql += sqlCondition;
			return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(entityClass), values);
		}else {
			return listAll();
		}
	}
	public List<T> listBySql(String sql, Object ...values) throws Exception{
		if(StringUtils.isNotBlank(sql)){
			if(values!=null&&values.length>=0){
				return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(entityClass), values);
			}else {
				return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(entityClass));
			}
		}
		return null;
	}
	public long getCount(String sql, QueryCondition queryCondition) throws Exception {
		if(queryCondition!=null&&queryCondition.getQueryString()!=null){
			String conditionSql = queryCondition.getQueryString()==null?"":queryCondition.getQueryString();
			if(StringUtils.isBlank(sql)){
				sql = BaseDaoUitl.getEntityCountSql(entityClass) + conditionSql;
			}else {
				sql = BaseDaoUitl.getCountSql(sql + conditionSql);
			}
		}else{
			sql = BaseDaoUitl.getCountSql(sql);
		}
		if(queryCondition!=null&&queryCondition.getQueryValues() != null && queryCondition.getQueryValues().length > 0 ){
			return jdbcTemplate.queryForLong(sql, queryCondition.getQueryValues());
		}else {
			return jdbcTemplate.queryForLong(sql);
		}
	}

	public PageData<T> getPageData(PageData<T> pageData, QueryCondition queryCondition) throws Exception {
		this.getPageData(pageData, BaseDaoUitl.getSelectSql(entityClass), queryCondition);
		return pageData;
	}

	public PageData<T> getPageData(PageData<T> pageData, String sql, QueryCondition queryCondition) throws Exception {
		long count = this.getCount(sql, queryCondition);
		pageData.setTotalCount(count);
		
		if(queryCondition!=null){
			if(StringUtils.isNotBlank(queryCondition.getQueryString())){
				sql += queryCondition.getQueryString();
			}
			if(StringUtils.isNotBlank(queryCondition.getOrderSql())){
				sql += queryCondition.getOrderSql();
			}
		}
		String pageSql = this.getPageSelectSql(sql, pageData);
		Object[] args = null;
		if(queryCondition != null && queryCondition.getQueryValues() !=null){
			args = new Object[queryCondition.getQueryValues().length];
			for (int i = 0,len = queryCondition.getQueryValues().length; i < len; i++) {
				args[i] = queryCondition.getQueryValues()[i];
			}
		}
		List<T> list = jdbcTemplate.query(pageSql, new BeanPropertyRowMapper<T>(entityClass), args);
		pageData.setPageItems(list);
		return pageData;
	}
	
	public PageData<Map<String, Object>> getPageMapData(PageData<Map<String, Object>> pageData, String sql, QueryCondition queryCondition) throws Exception{
		long count = this.getCount(sql, queryCondition);
		pageData.setTotalCount(count);
		
		if(queryCondition!=null){
			if(StringUtils.isNotBlank(queryCondition.getQueryString())){
				sql += queryCondition.getQueryString();
			}
			if(StringUtils.isNotBlank(queryCondition.getOrderSql())){
				sql += queryCondition.getOrderSql();
			}
		}
		String pageSql = this.getPageSelectSql(sql, pageData);
		Object[] args = null;
		if(queryCondition != null && queryCondition.getQueryValues() !=null){
			args = new Object[queryCondition.getQueryValues().length];
			for (int i = 0,len = queryCondition.getQueryValues().length; i < len; i++) {
				args[i] = queryCondition.getQueryValues()[i];
			}
		}
		List<Map<String, Object>> list = jdbcTemplate.queryForList(pageSql, args);
		pageData.setPageItems(list);
		return pageData;
	}

	/**
	 * 不同数据库类型分页语句，暂时支持Oracle，MySql，PostgreSQL数据库
	 * @param sql
	 * @param pageData
	 * @return
	 * @throws Exception
	 */
	private String getPageSelectSql(String sql, PageData pageData) throws Exception{
		if(dialect==null){
			if(Constants.ORACLE_DB.equals(Constants.SYS_DB_TYPE)){
				dialect = new OracleDialect();
			} else if (Constants.MYSQL_DB.equals(Constants.SYS_DB_TYPE)) {
				dialect = new MysqlDialect();
			}else if (Constants.POSTGRESQL_DB.equals(Constants.SYS_DB_TYPE)) {
				dialect = new PostgreSQLDialect();
			}else if(Constants.SQLSERVER_DB.equals(Constants.SYS_DB_TYPE)){
				dialect = new SQLServer2005Dialect();
			}
		}
		if(dialect==null){
			throw new DaoException("不支持的数据库类型!");
		}
		return dialect.getLimitSql(sql, pageData.getStartOffset(), pageData.getPageSize());
	}
	
	public JdbcTemplate getJdbcTemplate() {
		return this.jdbcTemplate;
	}

	public String getSequenceName() {
		return sequenceName;
	}

	public void setSequenceName(String sequenceName) {
		this.sequenceName = sequenceName;
	}
	/**
	 * 获取数据库的序列生成器，Oracle和PostgreSQL数据库
	 * @return
	 */
	private DataFieldMaxValueIncrementer getIncrementer() {
		if(Constants.ORACLE_DB.equals(Constants.SYS_DB_TYPE)){
			this.incrementer = new OracleSequenceMaxValueIncrementer((DataSource) SpringContextUtil.getBean("dataSource"), sequenceName);
		}else if(Constants.POSTGRESQL_DB.equals(Constants.SYS_DB_TYPE)){
			this.incrementer = new PostgreSQLSequenceMaxValueIncrementer((DataSource) SpringContextUtil.getBean("dataSource"), sequenceName);
		}
		return incrementer;
	}
}
